/*
STERLING SOFTWARE 
*/
/*
1)
*/

SELECT em_name, em_id
  FROM (SELECT em_name, em_id, DENSE_RANK() OVER(ORDER BY salary DESC) rk
          FROM employee
         WHERE location = 'Bengaluru')
 WHERE rk = 1;

/*
2)
*/

SELECT location
  FROM (SELECT location, RANK() OVER(ORDER BY salary DESC) rk
          FROM (SELECT AVG(salary) salary, location
                  FROM employee
                 GROUP BY location))
 WHERE rk = 1;

/*
3)
*/
SELECT * FROM employee 
WHERE (salary,location) IN (
SELECT AVG(salary) salary, location
                  FROM employee
                 GROUP BY location)
)

/*
4)
*/

SELECT 
	COUNT(DISTINCT CASE WHEN sex = "M" THEN 1 END) Male_CNT,
	COUNT(DISTINCT CASE WHEN sex = "F" THEN 1 END) Male_CNT
FROM 
	employee;

/*
5)
*/ --Correlated update
UPDATE emp e SET dept_name = (SELECT d.name FROM department d WHERE 
							 e.dept_id = d.dept_id);

/*
6)
*/

SELECT e.*,DENSE_RANK() OVER(ORDER BY salary DESC) rk
FROM   employee;

/*
ANSWER THE BELOW QUESTIONS
*/

/*
1)
a)	details of emp 405 Manoj Menon and 406 Ajith Murali

1)
b)	400,401

*/

/*
PLSQL anonymous block to print next ten date from today
*/

DECLARE
  i DATE := SYSDATE;
  j NUMBER := 1;
BEGIN

  LOOP
    j := j + 1;
  
    DBMS_OUTPUT.PUT_LINE(i);
    i := i + 1;
    EXIT WHEN j > 10;
  END LOOP;

END;
/

/*
Write A PLSQL to accept
2 arg and print the least one
*/

CREATE OR REPLACE FUNCTION f1(i IN NUMBER, j IN NUMBER)
RETURN NUMBER 
IS

BEGIN 
IF i >= j THEN 
	RETURN i;
ELSE
	RETURN j;
END IF;

END;
/

/*
TRIGGER 

*/

CREATE OR REPLACE TRIGGER t1
FOR INSERT ON emp
COMPOUND TRIGGER

AFTER STATEMENT
IS
BEGIN
UPDATE emp e SET e.dname =
(
SELECT	d.department_name
FROM	departments d
WHERE	e.department_id = d.department_id
)
WHERE e.dname IS NULL;
END AFTER STATEMENT;

END t1;
/